create database dm;
use dm;

create table dm.dm_intention_customer_statistics(
    time_dim_type string comment '时间维度类型',
    year_date STRING COMMENT '年',
    month_date STRING COMMENT '月',
    day_date STRING COMMENT '日',
    hour_date STRING COMMENT '时',

    origin_type STRING COMMENT '数据来源,线下0，线上1',
    clue_state STRING COMMENT '线索状态',

    grouptype string comment '业务类型',
    area string comment  '地区',
    itcast_subject_id int COMMENT '学科Id',
    itcast_subject_name string COMMENT '学科名称',
    itcast_school_id int COMMENT '校区Id',
    itcast_school_name string COMMENT '校区名称',
    tdepart_id INT COMMENT '直属部门',
    tdepart_name string COMMENT '部门名称',

    customer_total int comment '意向客户数'
)comment '客户意向多维宽表'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

create table dm.dm_clue_customer_statistics(
    time_dim_type string comment '时间维度类型',
    year_date STRING COMMENT '年',
    month_date STRING COMMENT '月',
    day_date STRING COMMENT '日',
    hour_date STRING COMMENT '时',

    origin_type STRING COMMENT '数据来源',
    clue_state STRING COMMENT '线索状态',

    clue_total int comment '有效线索量'
)comment '客户线索多维宽表'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

